from openpyxl import load_workbook
from copy import copy  # 用于深度复制对象
from openpyxl.styles import Font, Alignment

import trans
import config


def double_row_height(sheet):
    """
    将每个单元格的行高乘以2
    :param sheet: 要调整的表
    :return:
    """
    for row_dim in sheet.row_dimensions.values():
        if row_dim.height:  # 检查当前行是否有定义高度
            row_dim.height *= 2


def copy_cell_style(source_cell, target_cell):
    """
    复制单元格样式
    :param source_cell: 源单元格
    :param target_cell: 目标单元格
    :return:
    """
    if source_cell.has_style:
        target_cell.font = copy(source_cell.font)
        target_cell.fill = copy(source_cell.fill)
        target_cell.border = copy(source_cell.border)
        target_cell.alignment = copy(source_cell.alignment)
        target_cell.number_format = copy(source_cell.number_format)
        target_cell.protection = copy(source_cell.protection)


def get_sheet(workbook, sheet_name_key_word: str):
    sheet_names = workbook.sheetnames
    sheet_name = list(filter(lambda s: sheet_name_key_word in s, sheet_names))[0]
    print(sheet_name)
    sheet = workbook[sheet_name]
    return sheet


def set_font(cell, font_name: str = '宋体'):
    """
    仅更改字体名称，保持其他属性不变
    :param cell: 单元格对象
    :param font_name: 要设定的字体
    :return:
    """
    original_font = cell.font
    new_font = Font(
        name=font_name,  # 设置字体名称
        size=original_font.size,  # 保持原字体大小
        bold=original_font.bold,  # 保持原字体是否加粗
        italic=original_font.italic,  # 保持原字体是否斜体
        vertAlign=original_font.vertAlign,  # 保持原字体垂直对齐
        underline=original_font.underline,  # 保持原字体是否有下划线
        strike=original_font.strike,  # 保持原字体是否有删除线
        color=original_font.color  # 保持原字体颜色
    )
    return new_font


def format_font(cell):
    cell.font = set_font(cell, '宋体')
    cell.font = set_font(cell, 'Arial')


if __name__ == '__main__':
    # 加载第一个工作簿
    workbook1 = load_workbook(config.input_file1)
    
    sheet1 = get_sheet(workbook1, config.target_sheet_key_word1)
    
    # 加载第二个工作簿
    workbook2 = load_workbook(config.input_file2)
    sheet2 = get_sheet(workbook2, config.target_sheet_key_word2)
    
    # 获取两个工作表的最大行和列
    max_row = max(sheet1.max_row, sheet2.max_row)
    max_col = max(sheet1.max_column, sheet2.max_column)
    
    # 遍历指定的列并将workbook2的内容追加到workbook1中
    for col_letter in config.trans_col_list:
        for row in range(2, sheet1.max_row + 1):
            cell1 = sheet1[f"{col_letter}{row}"]
            cell2 = sheet2[f"{col_letter}{row}"]
            
            # 追加单元格内容
            content1 = cell1.value if cell1.value is not None else ""
            content2 = cell2.value if cell2.value is not None else ""
            content1 = content1.strip()
            content2 = content2.strip().replace('Severus', 'Safenux').replace('severus', 'Safenux')
            
            if content1 in config.skip_list or content2 in config.skip_list:
                continue
            
            if content1.startswith('=') or content2.startswith('='):
                continue
            
            # 检测翻译出错的行
            if content1 == content2:
                print(row, content1, content2)
                translator = trans.BaiduTranslator()
                content2 = translator.translate(content1, 'zh', 'en', 'it').strip()
                print(content2)
            try:
                cell1.value = content1 + '\n' + content2
            except AttributeError as e:
                print(e)
            
            cell1.alignment = Alignment(wrapText=True)
    
    double_row_height(sheet1)
    
    # 遍历工作簿中的所有工作表
    for sheet in workbook1.worksheets:
        # 遍历每个工作表中的每一行和每一个单元格
        for row in sheet.iter_rows():
            for cell in row:
                format_font(cell)
    
    # 保存修改后的工作簿
    workbook1.save(config.output_file)
